"""
主应用入口和API路由
"""
from fastapi import FastAPI, UploadFile, File, HTTPException
from fastapi.middleware.cors import CORSMiddleware
from fastapi.staticfiles import StaticFiles
import uuid

# 导入项目模块
import config
import models
from database import init_db, import_data_file, execute_query, create_database_if_not_exists, get_database_schema
from ai_service import generate_sql, extract_sql_from_ai_response, generate_explanation, detect_query_intent, generate_general_response, refine_sql_with_feedback
from utils import generate_message_id, add_to_chat_history

# 创建FastAPI应用
app = FastAPI(title="AI数据库问答式工具")

# 允许跨域请求
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

# 挂载静态文件目录
app.mount("/static", StaticFiles(directory="static"), name="static")

# 添加专门的 favicon 路由
@app.get('/favicon.ico')
async def favicon():
    from fastapi.responses import FileResponse
    return FileResponse("static/favicon.ico")

# 启动时创建数据库
@app.on_event("startup")
async def startup_event():
    # 确保数据库存在
    create_database_if_not_exists()

    # 尝试获取数据库结构
    try:
        if not config.DB_SCHEMA:
            schema = get_database_schema()
            if schema:
                config.DB_SCHEMA = schema
                print("已从现有数据库加载模式")
    except Exception as e:
        print(f"加载数据库模式失败: {e}")

# 1. 数据库管理接口
@app.post("/api/database/import")
async def import_data(file: UploadFile = File(...)):
    """从CSV/Excel文件导入数据"""
    try:
        records_imported = await import_data_file(file)
        
        return {
            "success": True,
            "records_imported": records_imported,
            "message": "数据导入成功"
        }
    except HTTPException as e:
        # 重新引发HTTP异常
        raise e
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"导入数据错误: {str(e)}")

# 2. 聊天接口

@app.post("/api/chat/message")
async def process_message(message: models.ChatMessage):
    """处理用户消息并返回AI响应"""    
    try:
        # 确保有可用的数据库模式
        db_schema = None
        
        if config.USING_IMPORTED_DATA and config.DB_SCHEMA:
            db_schema = config.DB_SCHEMA
        else:
            # 尝试从配置的数据库获取模式
            db_schema = get_database_schema()
            
        if not db_schema:
            raise HTTPException(status_code=400, detail="无可用的数据库结构，请导入数据或配置数据库")

        # 将用户消息添加到历史
        add_to_chat_history(message.message, is_user=True)

        # 检测用户意图
        intent = detect_query_intent(message.message)
        print(f"检测到用户意图: {intent}")

        # 生成消息ID
        message_id = generate_message_id()

        # 如果是一般对话，不执行SQL查询
        if intent == "general_conversation":
            # 生成普通回答
            response_text = generate_general_response(message.message)
            
            # 创建响应
            response = {
                "message_id": message_id,
                "response": response_text,
                "sql_generated": "",
                "results": []
            }
            
            # 添加AI回复到聊天历史
            add_to_chat_history({
                "content": response_text,
                "sql_generated": "",
                "results": []
            }, is_user=False)
            
            return response
        
        # 从自然语言生成SQL查询
        print(f"生成SQL查询，问题: {message.message}")
        raw_response = generate_sql(message.message, config.DB_SCHEMA, message.options)
        sql_query = extract_sql_from_ai_response(raw_response)
        print(f"第一次生成的SQL: {sql_query}")
        
        # 执行SQL查询
        print(f"执行第一次SQL查询...")
        query_results, error = execute_query(
            sql_query, 
            message.options.get("max_results", 100)
        )
        
        print(f"第一次查询结果: {'成功' if not error else '失败'}, 结果数量: {len(query_results) if query_results else 0}")
        if error:
            print(f"第一次查询错误: {error}")
        
        # 只有当查询出错或没有返回结果时才进行二次执行
        if error or (query_results is not None and len(query_results) == 0):
            print("触发二次执行条件，开始优化SQL...")
            # 使用反馈优化SQL
            refined_sql = refine_sql_with_feedback(
                message.message, 
                sql_query, 
                error, 
                query_results, 
                config.DB_SCHEMA
            )
            
            # 提取并执行优化后的SQL
            refined_sql = extract_sql_from_ai_response(refined_sql)
            print(f"优化后的SQL: {refined_sql}")
            
            # 只有当优化后的SQL与原SQL不同时才执行
            if refined_sql and refined_sql.strip() != sql_query.strip():
                print("优化后的SQL与原SQL不同，执行优化后的SQL...")
                refined_results, refined_error = execute_query(
                    refined_sql,
                    message.options.get("max_results", 100)
                )
                
                print(f"二次查询结果: {'成功' if not refined_error else '失败'}, 结果数量: {len(refined_results) if refined_results else 0}")
                if refined_error:
                    print(f"二次查询错误: {refined_error}")
                
                # 只有当优化后的查询成功且有结果时，才使用优化后的结果
                if not refined_error and refined_results and len(refined_results) > 0:
                    print("使用优化后的SQL结果")
                    sql_query = refined_sql
                    query_results = refined_results
                    error = None
                else:
                    print("优化后的SQL没有改善结果，保留原SQL")
            else:
                print("优化后的SQL与原SQL相同或为空，不执行二次查询")
        else:
            print("第一次查询成功且有结果，不需要二次执行")
        
        # 生成结果解释
        explanation = generate_explanation(message.message, query_results, sql_query, error)
        
        # 创建响应
        response = {
            "message_id": message_id,
            "response": explanation,
            "sql_generated": sql_query,
            "results": query_results,
            "had_second_execution": sql_query != raw_response  # 添加标志指示是否进行了二次执行
        }
        
        # 添加AI回复到聊天历史
        add_to_chat_history({
            "content": explanation,
            "sql_generated": sql_query,
            "results": query_results
        }, is_user=False)
        
        return response
    except Exception as e:
        print(f"处理消息错误: {str(e)}")
        import traceback
        traceback.print_exc()
        raise HTTPException(status_code=500, detail=f"处理消息错误: {str(e)}")

@app.get("/api/chat/history")
async def get_chat_history():
    """获取聊天历史"""
    return {"messages": config.CHAT_HISTORY}

# 3. 查询选项接口

@app.get("/api/options")
async def get_options():
    """获取可用的查询选项"""
    options = [
        {
            "id": "allow_joins",
            "name": "多表联查",
            "description": "允许AI在多个表之间执行JOIN操作",
            "default": False
        },
        {
            "id": "complex_aggregations",
            "name": "复杂聚合",
            "description": "允许复杂的GROUP BY和聚合函数",
            "default": False
        }
    ]
    
    return {"options": options}

@app.put("/api/options")
async def update_options(options_update: models.OptionsUpdate):
    """更新查询选项设置"""
    try:
        # 更新选项
        for key, value in options_update.options.items():
            if key in config.QUERY_OPTIONS:
                config.QUERY_OPTIONS[key] = value
        
        return {
            "success": True,
            "message": "选项更新成功",
            "current_options": config.QUERY_OPTIONS
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"更新选项错误: {str(e)}")

# AI模型接口
@app.get("/api/ai-model")
async def get_ai_model():
    """获取当前AI模型设置"""
    return {
        "current_model": config.ACTIVE_AI_MODEL,
        "available_models": config.AI_MODELS
    }

@app.put("/api/ai-model")
async def update_ai_model(model_update: dict):
    """更新AI模型设置"""
    try:
        model = model_update.get("model")
        if model not in config.AI_MODELS:
            raise HTTPException(status_code=400, detail="不支持的AI模型")
        
        config.ACTIVE_AI_MODEL = model
        
        return {
            "success": True,
            "model_name": config.AI_MODELS[model]
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

# 根端点 - 修改返回HTML页面
@app.get("/")
async def root():
    """返回HTML页面"""
    from fastapi.responses import FileResponse
    return FileResponse("static/index.html")

# 主执行代码
if __name__ == "__main__":
    import uvicorn
    # 启动FastAPI服务器
    uvicorn.run(app, host="127.0.0.1", port=8000)
